home *** CD-ROM | disk | FTP | other *** search
/ Personal Computer World 2008 February / PCWFEB08.iso / Software / Resources / Developers / PSPad 4.5.2 / pspad452inst_en.exe / {app} / Script / JScript / SQLFormat.js < prev    next >
Encoding:
JavaScript  |  2006-03-24  |  8.7 KB  |  190 lines
  1. /*****************************************************************************
  2.  * This software is provided 'as-is', without any express or implied warranty.
  3.  * In no event will the authors be held liable for any damages arising from
  4.  * the use of this software.
  5.  *
  6.  * Permission is granted to anyone to use this software for any purpose,
  7.  * including commercial applications, and to alter it and redistribute it freely,
  8.  * subject to the following restrictions:
  9.  *
  10.  * 1. The origin of this software must not be misrepresented; you must not claim
  11.  * that you wrote the original software. If you use this software in a product,
  12.  * an acknowledgment in the product documentation would be appreciated but is
  13.  * not required.
  14.  *
  15.  * 2. Altered source versions must be plainly marked as such, and must not be
  16.  * misrepresented as being the original software.
  17.  *
  18.  * 3. This notice may not be removed or altered from any source distribution.
  19.  *
  20.  * The Original Code is: AnySoft Informatica
  21.  *                       Marcelo Leite (aka Mr. Milk)
  22.  *                       2005-12-11 mrmilk@anysoft.com.br
  23.  *
  24.  * The Initial Developer of the Original Code is AnySoft Informatica Ltda.
  25.  * Portions created by AnySoft are Copyright (C) 2005 AnySoft Informatica Ltda
  26.  * All Rights Reserved.
  27.  *
  28.  ********************************************************************************/
  29. var module_name = "SQLFormat";
  30. var module_ver = "1.00";
  31.  
  32. var keywords = [
  33.   "COUNT",                         "FETCH",                         "PRIMARY",
  34.   "AVG",                           "FLOAT",                         "PROCEDURE",
  35.   "MAX",                           "FOR",                           "PURGE",
  36.   "MIN",                           "FORCE",                         "READ",
  37.   "VAR",                           "FOREIGN",                       "READS",
  38.   "STD",                           "FROM",                          "REAL",
  39.   "CONCAT",                        "FULLTEXT",                      "REFERENCES",
  40.   "ADD",                           "GOTO",                          "REGEXP",
  41.   "ALL",                           "GRANT",                         "RENAME",
  42.   "ALTER",                         "GROUP",                         "REPEAT",
  43.   "ANALYZE",                       "HAVING",                        "REPLACE",
  44.   "AND",                           "HIGH_PRIORITY",                 "REQUIRE",
  45.   "AS",                            "HOUR_MICROSECOND",              "RESTRICT",
  46.   "ASC",                           "HOUR_MINUTE",                   "RETURN",
  47.   "ASENSITIVE",                    "HOUR_SECOND",                   "REVOKE",
  48.   "BEFORE",                        "IF",                            "RIGHT",
  49.   "BETWEEN",                       "IGNORE",                        "RLIKE",
  50.   "BIGINT",                        "IN",                            "SCHEMA",
  51.   "BINARY",                        "INDEX",                         "SCHEMAS",
  52.   "BLOB",                          "INFILE",                        "SECOND_MICROSECOND",
  53.   "BOTH",                          "INNER",                         "SELECT",
  54.   "BY",                            "INOUT",                         "SENSITIVE",
  55.   "CALL",                          "INSENSITIVE",                   "SEPARATOR",
  56.   "CASCADE",                       "INSERT",                        "SET",
  57.   "CASE",                          "INT",                           "SHOW",
  58.   "CHANGE",                        "INTEGER",                       "SMALLINT",
  59.   "CHAR",                          "INTERVAL",                      "SONAME",
  60.   "CHARACTER",                     "INTO",                          "SPATIAL",
  61.   "CHECK",                         "IS",                            "SPECIFIC",
  62.   "COLLATE",                       "ITERATE",                       "SQL",
  63.   "COLUMN",                        "JOIN",                          "SQLEXCEPTION",
  64.   "CONDITION",                     "KEY",                           "SQLSTATE",
  65.   "CONNECTION",                    "KEYS",                          "SQLWARNING",
  66.   "CONSTRAINT",                    "KILL",                          "SQL_BIG_RESULT",
  67.   "CONTINUE",                      "LEADING",                       "SQL_CALC_FOUND_ROWS",
  68.   "CONVERT",                       "LEAVE",                         "SQL_SMALL_RESULT",
  69.   "CREATE",                        "LEFT",                          "SSL",
  70.   "CROSS",                         "LIKE",                          "STARTING",
  71.   "CURRENT_DATE",                  "LIMIT",                         "STRAIGHT_JOIN",
  72.   "CURRENT_TIME",                  "LINES",                         "TABLE",
  73.   "CURRENT_TIMESTAMP",             "LOAD",                          "TERMINATED",
  74.   "CURRENT_USER",                  "LOCALTIME",                     "THEN",
  75.   "CURSOR",                        "LOCALTIMESTAMP",                "TINYBLOB",
  76.   "DATABASE",                      "LOCK",                          "TINYINT",
  77.   "DATABASES",                     "LONG",                          "TINYTEXT",
  78.   "DAY_HOUR",                      "LONGBLOB",                      "TO",
  79.   "DAY_MICROSECOND",               "LONGTEXT",                      "TRAILING",
  80.   "DAY_MINUTE",                    "LOOP",                          "TRIGGER",
  81.   "DAY_SECOND",                    "LOW_PRIORITY",                  "TRUE",
  82.   "DEC",                           "MATCH",                         "UNDO",
  83.   "DECIMAL",                       "MEDIUMBLOB",                    "UNION",
  84.   "DECLARE",                       "MEDIUMINT",                     "UNIQUE",
  85.   "DEFAULT",                       "MEDIUMTEXT",                    "UNLOCK",
  86.   "DELAYED",                       "MIDDLEINT",                     "UNSIGNED",
  87.   "DELETE",                        "MINUTE_MICROSECOND",            "UPDATE",
  88.   "DESC",                          "MINUTE_SECOND",                 "USAGE",
  89.   "DESCRIBE",                      "MOD",                           "USE",
  90.   "DETERMINISTIC",                 "MODIFIES",                      "USING",
  91.   "DISTINCT",                      "NATURAL",                       "UTC_DATE",
  92.   "DISTINCTROW",                   "NOT",                           "UTC_TIME",
  93.   "DIV",                           "NO_WRITE_TO_BINLOG",            "UTC_TIMESTAMP",
  94.   "DOUBLE",                        "NULL",                          "VALUES",
  95.   "DROP",                          "NUMERIC",                       "VARBINARY",
  96.   "DUAL",                          "ON",                            "VARCHAR",
  97.   "EACH",                          "OPTIMIZE",                      "VARCHARACTER",
  98.   "ELSE",                          "OPTION",                        "VARYING",
  99.   "ELSEIF",                        "OPTIONALLY",                    "WHEN",
  100.   "ENCLOSED",                      "OR",                            "WHERE",
  101.   "ESCAPED",                       "ORDER",                         "WHILE",
  102.   "EXISTS",                        "OUT",                           "WITH",
  103.   "EXIT",                          "OUTER",                         "WRITE",
  104.   "EXPLAIN",                       "OUTFILE",                       "XOR",
  105.   "FALSE",                         "PRECISION",                     "YEAR_MONTH",
  106.                                                                 "ZEROFILL" ];
  107. function SQLNoIndent() {
  108.   SQLFormat(false);
  109. }
  110.  
  111. function SQLIndent() {
  112.   SQLFormat(true);
  113. }
  114.  
  115. function SQLFormat(indent) {
  116.  
  117.   var ed  = newEditor();
  118.   ed.assignActiveEditor();
  119.   
  120.   var sql = ed.selText();
  121.   if(sql=='') {
  122.     sql = ed.Text();
  123.   }
  124.   sql = sql.replace(/\s+/ig, " ");
  125.   for(var i=0;i<keywords.length;i++) {
  126.     rex = new RegExp("\\b"+keywords[i]+"\\b", "ig");
  127.     sql = sql.replace(rex, keywords[i]);
  128.   }
  129.     
  130.   sql = sql.replace(/\s*\b(UNION ALL|UNION|SELECT DISTINCT|SELECT|UPDATE|INSERT|DELETE|FROM|LEFT JOIN|RIGHT JOIN|INNER JOIN|WHERE|HAVING|ORDER BY|GROUP BY|LIMIT)\b\s*/ig, "\n$1\n\t");
  131.   sql = sql.replace(/\s*\b(ON)\b\s*/ig, "\n\t$1\n\t\t");
  132.  
  133.   if(indent) {
  134.     var indent = 0;
  135.     var pos = 0;
  136.     while(pos<sql.length) {
  137.       var tab = "";
  138.       var open  = sql.indexOf("(", pos);
  139.       var close = sql.indexOf(")", pos);
  140.  
  141.       if(open<close && open!=-1) {
  142.         pos = open;
  143.         indent++;
  144.         for(var i=0;i<=indent;i++)
  145.           tab += "\t";
  146.         var sql_a = sql.substr(0, pos);
  147.         var sql_b = "(\n"+tab+sql.substr(pos+1);
  148.         sql = sql_a+sql_b;
  149.         pos += tab.length+2;
  150.       }
  151.       else if(close!=-1) {
  152.         pos = close;
  153.         for(var i=0;i<indent;i++)
  154.           tab += "\t";
  155.         var sql_a = sql.substr(0, pos);
  156.         var sql_b = "\n"+tab+")"+sql.substr(pos+1);
  157.         sql = sql_a+sql_b;
  158.         pos += tab.length+2;
  159.         indent--;
  160.       }
  161.       else
  162.         break;
  163.     }
  164.     sql = sql.replace(/(\t+)([^\n]*)((?:,)\s*)/mig, "$1$2$3\n$1");
  165.     sql = sql.replace(/([^)]\s*)(,(?!\s*\n)\s*)/ig,  "$1$2\n\t");
  166.   }
  167.   else
  168.     sql = sql.replace(/\s*(,)\s*/ig,  "$1\n\t");
  169.   
  170.   sql = sql.replace(/(\t+)([^\n]*)(\b(?:OR)\s+)/mig, "$1$2\n$1$3\n$1\t");
  171.   sql = sql.replace(/(\t+)([^\n]*)(\b(?:AND|THEN|ELSE)\s*\()/mig, "$1$2\n$1$3");
  172.   while(sql.search(/(\t+)([^\t]+)(?=((AND|THEN|ELSE)\b\s+)(.+)).*/mig)!=-1)
  173.     sql = sql.replace(/(\t+)([^\t]+)(?=((?:AND|THEN|ELSE)\b\s+)(.+)).*/mig, "$1$2\n$1$3\n$1\t$4");
  174.     
  175.   var title = "\n/* ------------- SQL FORMATTER by Milk ------------- */\n";
  176.   sql = title+sql+"\n"+title+"\n";
  177.  
  178.   var txt = ed.selText();
  179.   if(txt=='')
  180.     ed.text(sql);
  181.   else
  182.     ed.selText(sql);
  183.  
  184. }
  185.  
  186. function Init(){
  187.   addMenuItem("SQL Format w/ Indent", "Format code", "SQLIndent");
  188.   addMenuItem("SQL Format w/o Indent", "Format code", "SQLNoIndent");
  189. }
  190.